package com.loser.module.sql;

import com.loser.module.sql.entity.AggregationField;
import com.loser.module.sql.entity.CustomGroupOperation;
import com.loser.module.sql.entity.ParsedQuery;
import com.loser.module.sql.utils.TypeConverter;
import org.bson.Document;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationOperation;
import org.springframework.data.mongodb.core.aggregation.AggregationResults;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static org.springframework.data.mongodb.core.aggregation.Aggregation.newAggregation;

public class AggregationConverter {

    private final MongoTemplate mongoTemplate;

    public AggregationConverter(MongoTemplate mongoTemplate) {
        this.mongoTemplate = mongoTemplate;
    }

    public <T> List<T> convertAndExecute(String mysqlQuery, Class<T> resultClass) {

        // 解析 MySQL 查询
        ParsedQuery parsedQuery = parseMysqlQuery(mysqlQuery);
        if (parsedQuery == null) {
            throw new IllegalArgumentException("Invalid MySQL query");
        }

        // 构建 MongoDB 聚合操作
        List<AggregationOperation> operations = new ArrayList<>();

        // 添加 WHERE 子句支持
        if (parsedQuery.getWhereClause() != null) {
            operations.add(context -> new Document("$match", parsedQuery.getWhereClause()));
        }
        Document groupFields = new Document("_id", "$" + parsedQuery.getGroupByField());

        for (AggregationField aggregationField : parsedQuery.getAggregations()) {
            String field = "$" + aggregationField.getField();
            String alias = aggregationField.getAlias();
            switch (aggregationField.getFunction()) {
                case "SUM":
                    groupFields.append(alias, new Document("$sum", field));
                    break;
                case "AVG":
                    groupFields.append(alias, new Document("$avg", field));
                    break;
                case "COUNT":
                    groupFields.append(alias, new Document("$sum", 1));
                    break;
                case "MIN":
                    groupFields.append(alias, new Document("$min", field));
                    break;
                case "MAX":
                    groupFields.append(alias, new Document("$max", field));
                    break;
                default:
                    throw new IllegalArgumentException("Unsupported aggregation function: " + aggregationField.getFunction());
            }
        }

        operations.add(new CustomGroupOperation(groupFields));


        Aggregation aggregation = newAggregation(operations);
        AggregationResults<T> results = mongoTemplate.aggregate(aggregation, parsedQuery.getCollectionName(), resultClass);

        return results.getMappedResults();
    }

    private ParsedQuery parseMysqlQuery(String mysqlQuery) {

        // 简单的 SQL 解析示例，只处理特定格式的聚合查询
        Pattern pattern = Pattern.compile("SELECT (.+) FROM (.+?) (?:WHERE (.+))? GROUP BY (.+)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(mysqlQuery);

        if (matcher.find()) {
            String[] fields = matcher.group(1).split(",");
            String collectionName = matcher.group(2).trim();
            String whereClause = matcher.group(3); // 可能为 null
            String groupByField = matcher.group(4).trim();
            List<AggregationField> aggregations = new ArrayList<>();

            for (String field : fields) {
                String[] parts = field.trim().split("\\s+AS\\s+", 2);
                String expr = parts[0].trim();
                String alias = parts.length > 1 ? parts[1].trim() : expr;

                String function = null;
                String fieldName = null;
                Matcher funcMatcher = Pattern.compile("(\\w+)\\((.+)\\)").matcher(expr);
                if (funcMatcher.find()) {
                    function = funcMatcher.group(1).toUpperCase();
                    fieldName = funcMatcher.group(2).trim();
                }

                if (function != null) {
                    aggregations.add(new AggregationField(function, fieldName, alias));
                }
            }

            // 构建 where 条件
            Document whereCondition = null;
            if (whereClause != null && !whereClause.isEmpty()) {
                whereCondition = parseWhereClause(whereClause);
            }

            return new ParsedQuery(collectionName, groupByField, aggregations, whereCondition);
        }

        return null;

    }

    private Document parseWhereClause(String whereClause) {

        // 去除首尾空格
        whereClause = whereClause.trim();

        // 检查是否包含 AND 或 OR 运算符
        Pattern patternAnd = Pattern.compile("\\bAND\\b", Pattern.CASE_INSENSITIVE);
        Pattern patternOr = Pattern.compile("\\bOR\\b", Pattern.CASE_INSENSITIVE);

        Matcher matcherAnd = patternAnd.matcher(whereClause);
        Matcher matcherOr = patternOr.matcher(whereClause);

        if (matcherAnd.find() || matcherOr.find()) {
            // 包含 AND 或 OR 运算符的复杂条件
            return parseComplexConditions(whereClause);
        } else {
            // 单个条件情况
            return parseSingleCondition(whereClause);
        }
    }

    private Document parseComplexConditions(String whereClause) {
        List<Document> conditionList = new ArrayList<>();

        // 支持 AND 和 OR 运算符
        Pattern patternAnd = Pattern.compile("(.+?)\\s+AND\\s+(.+)", Pattern.CASE_INSENSITIVE);
        Pattern patternOr = Pattern.compile("(.+?)\\s+OR\\s+(.+)", Pattern.CASE_INSENSITIVE);

        Matcher matcherAnd = patternAnd.matcher(whereClause);
        Matcher matcherOr = patternOr.matcher(whereClause);

        if (matcherAnd.find()) {
            String leftCondition = matcherAnd.group(1).trim();
            String rightCondition = matcherAnd.group(2).trim();
            conditionList.add(parseSingleCondition(leftCondition));
            conditionList.add(parseComplexConditions(rightCondition));
            return new Document("$and", conditionList);
        } else if (matcherOr.find()) {
            String leftCondition = matcherOr.group(1).trim();
            String rightCondition = matcherOr.group(2).trim();
            conditionList.add(parseSingleCondition(leftCondition));
            conditionList.add(parseComplexConditions(rightCondition));
            return new Document("$or", conditionList);
        } else {
            return parseSingleCondition(whereClause);
        }
    }

    private Document parseSingleCondition(String condition) {
        // 支持比较运算符和字段值
        String[] parts = condition.split("\\s+");
        if (parts.length != 3) {
            throw new IllegalArgumentException("Invalid condition format: " + condition);
        }
        String fieldName = parts[0].trim();
        String operator = parts[1].trim();
        String strObjc = parts[2].trim().replace("'", "");
        Object value = TypeConverter.convert(strObjc);

        switch (operator) {
            case "=":
                return new Document(fieldName, value);
            case ">":
                return new Document(fieldName, new Document("$gt", value));
            case "<":
                return new Document(fieldName, new Document("$lt", value));
            case ">=":
                return new Document(fieldName, new Document("$gte", value));
            case "<=":
                return new Document(fieldName, new Document("$lte", value));
            case "!=":
                return new Document(fieldName, new Document("$ne", value));
            default:
                throw new IllegalArgumentException("Unsupported operator: " + operator);
        }
    }


}
